ALTER                 function fn_load_prevoius_lineitems (@salesorder_id int)

returns  @fn_result Table(COMMON_Product_ID int,
                          COMMON_Line_Item_No int,
			  COMMON_Qty int,
                          COMMON_Unit_Cost float,
                          COMMON_Amount float,
                          COMMON_Requested_Date datetime,
			  COMMON_Change_Number int,
			  ChangeNo int, 
			  COMMON_Status varchar(50),
			  COMMON_Line_Item_Id int,
			  Customer_PartNumber varchar(50),
			  Label_Number varchar(50),
			  PART_NUMBER VARCHAR(50),
			  MKTG_PART_NUMBER VARCHAR(50),
			  COOL_DESCRIPTION VARCHAR(200) )

as
begin
	declare @common_product_id int
        declare @common_line_item_no int
        declare @common_line_item_id int
	declare @common_qty int
	declare @common_unit_cost float
	declare @common_amount float
        declare @common_requested_date datetime
        declare @common_change_number int
	declare @customer_partnumber varchar(50)
	declare @customer_Id int
	declare @label_number varchar(50)
	declare @odm_id int

	declare @change_orders_cursor CURSOR
	declare @cancel_orders_cursor CURSOR
        declare @shipped_orders_cursor CURSOR
	DECLARE @change_no int
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @MKTG_PART_NUMBER VARCHAR(50)
	DECLARE @COOL_DESCRIPTION VARCHAR(200)

	declare @result Table(COMMON_Product_ID int,
			COMMON_Line_Item_No int,
			COMMON_Qty int,
                        COMMON_Unit_Cost float,
                        COMMON_Amount float,
                        COMMON_Requested_Date datetime,
			COMMON_Change_Number int,
			ChangeNo int, 
			COMMON_Status varchar(50),
		        COMMON_Line_Item_Id int,
			Customer_PartNumber varchar(50),
			Label_Number varchar(50),
			PART_NUMBER VARCHAR(50),
			MKTG_PART_NUMBER VARCHAR(50),
			COOL_DESCRIPTION VARCHAR(200))

	-- Added by Sarada on 18th Feb 2004
	SELECT @customer_Id = Customer_Id, @odm_id = SO_Odm_id FROM Sales_Order WHERE SalesOrder_Id = @salesorder_id

SET @change_orders_cursor = CURSOR FOR 
    SELECT Product_ID ,CCO_Line_Item_No,CCO_Qty , CCO_Unit_Cost,CCO_Amount,CCO_Requested_Date,CCO_Change_Number,ChangeNo, LineItem_ID, CCO_CUSTOMER_PARTNUMBER
FROM Customer_Change_Order_LineItem WHERE SalesOrder_Id =  @salesorder_id

SET @cancel_orders_cursor = CURSOR FOR 
    SELECT Product_ID,CCOL_Line_Item_No,CCOL_Qty , CCOL_Unit_Cost,CCOL_Amount,CCOL_Requested_Date,CCOL_Change_Number, LineItem_ID
FROM Customer_Cancel_Order_LineItem WHERE SalesOrder_Id =  @salesorder_id


SET @shipped_orders_cursor = CURSOR FOR 
   SELECT Product_ID,CSOL_Line_Item_No,CSOL_Qty , CSOL_Unit_Cost,CSOL_Amount,CSOL_Requested_Date, LineItem_ID
FROM Customer_Shipped_Order_LineItem WHERE SalesOrder_Id =  @salesorder_id


open @change_orders_cursor		

	fetch next from  @change_orders_cursor into  @common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
        @common_amount ,@common_requested_date ,@common_change_number, @change_no ,@common_line_item_id, @customer_partnumber

	while @@fetch_status = 0
	begin
		 -- Added by Sarada on 18th Feb 2004 to display customer partNumber in previousLineitems  of pending orders screen
		 --SET @customer_partnumber = null
		 SET @label_number = null
		 --select @customer_partnumber = customer_partnumber from Customer_PartNumbers where product_id = @common_product_id and Customer_ID = @customer_id
             	 select @label_number = Label_Number from Customer_PartNumbers where product_id = @common_product_id and Customer_ID = @customer_id and ODM_Id = @odm_id

		DECLARE @max_ch_no int
		select @max_ch_no = max(ChangeNo) from Customer_Change_Order_LineItem where SalesOrder_ID = @salesorder_id and LineItem_ID = @common_line_item_id
		
		if(@max_ch_no > 0)
		begin
			if(@change_no <> @max_ch_no)
			begin
				SELECT @PART_NUMBER = Part_Number, @MKTG_PART_NUMBER = P_Marketing_Part_Number FROM PRODUCT WHERE PRODUCT_ID = @common_product_id
				SELECT @COOL_DESCRIPTION = COOL_DESCRIPTION FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE LINEITEM_ID = @common_line_item_id

				 insert into @result (COMMON_Product_ID ,COMMON_Line_Item_No ,COMMON_Qty ,
                        		COMMON_Unit_Cost ,COMMON_Amount ,COMMON_Requested_Date,
					COMMON_Change_Number,ChangeNo,COMMON_Status,COMMON_Line_Item_Id,Customer_PartNumber,Label_Number, PART_NUMBER, MKTG_PART_NUMBER, COOL_DESCRIPTION)
	         		values (@common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
                 	 		@common_amount ,@common_requested_date ,@common_change_number,@change_no,'CHANGED', @common_line_item_id, @customer_partnumber,@label_number, @PART_NUMBER, @MKTG_PART_NUMBER, @COOL_DESCRIPTION )
			end
		end
		SET @PART_NUMBER = NULL
		SET @MKTG_PART_NUMBER = NULL
		SET @COOL_DESCRIPTION = NULL
	        fetch next from @change_orders_cursor into @common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
               @common_amount ,@common_requested_date ,@common_change_number ,@change_no,@common_line_item_id, @customer_partnumber

	end 
close @change_orders_cursor
deallocate @change_orders_cursor

/* commented by Sarada on 18th Feb 2004 as we are not displaying Cancelled lineitems and shipped lineitems in Pending ORders screen */
/*open @cancel_orders_cursor		

	fetch next from  @cancel_orders_cursor into  @common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
        @common_amount ,@common_requested_date ,@common_change_number, @common_line_item_id

	while @@fetch_status = 0
	begin
		 SET @customer_partnumber = null
		 SELECT @customer_partnumber = Customer_PartNumber FROM Customer_PartNumbers WHERE Product_Id = @common_product_id and Customer_Id = @customer_Id

		 insert  @result (COMMON_Product_ID ,COMMON_Line_Item_No ,COMMON_Qty ,
                        COMMON_Unit_Cost ,COMMON_Amount ,COMMON_Requested_Date,
			COMMON_Change_Number,COMMON_Status,COMMON_Line_Item_Id,Customer_PartNumber )
	         values (@common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
                  @common_amount ,@common_requested_date ,@common_change_number,'CANCELLED', @common_line_item_id, @customer_partnumber)


	        fetch next from @cancel_orders_cursor into @common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
               @common_amount ,@common_requested_date ,@common_change_number, @common_line_item_id

	end 
close @cancel_orders_cursor
deallocate @cancel_orders_cursor


open @shipped_orders_cursor		

	fetch next from  @shipped_orders_cursor into  @common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
        @common_amount ,@common_requested_date, @common_line_item_id

	while @@fetch_status = 0
	begin
		 SET @customer_partnumber = null
		 SELECT @customer_partnumber = Customer_PartNumber FROM Customer_PartNumbers WHERE Product_Id = @common_product_id and Customer_Id = @customer_Id

		 insert  @result (COMMON_Product_ID ,COMMON_Line_Item_No ,COMMON_Qty ,
                        COMMON_Unit_Cost ,COMMON_Amount ,COMMON_Requested_Date,
			COMMON_Change_Number,COMMON_Status,COMMON_Line_Item_Id,Customer_PartNumber )
	         values (@common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
                  @common_amount ,@common_requested_date ,0,'SHIPPED', @common_line_item_id, @customer_partnumber)


	        fetch next from @shipped_orders_cursor into @common_product_id,@common_line_item_no ,@common_qty,@common_unit_cost,
               @common_amount ,@common_requested_date, @common_line_item_id

	end 
close @shipped_orders_cursor
deallocate @shipped_orders_cursor
*/
  insert @fn_result select * from @result order by COMMON_Line_Item_No,ChangeNo
 --where COMMON_Change_Number > 0 and COMMON_Change_Number <> null order by COMMON_Line_Item_No,COMMON_Change_Number

  return 

END




